use master
go
create database GoodsDB
on
(
	name='GoodsDB',
	filename='D:\GoodsDB.mdf',
	size=10,
	maxsize=100,
	filegrowth=10
)
log on
(
	name='GoodsDB_log',
	filename='D:\GoodsDB_log.mdf',
	size=10,
	maxsize=100,
	filegrowth=10
)
go

use GoodsDB
go

create table GoodsType
(
	TypeID int primary key identity(1,1) not null,
	TypeName nvarchar(20) not null
)

create table GoodsInfo
(
	GoodsID int primary key identity(1,1) not null,
	GoodsName nvarchar(20) not null,
	GoodsColor nvarchar(20) not null,
	GoodsBrand nvarchar(20),
	GoodsMoney money not null,
	TypeID int references GoodsType(TypeID)
)

insert into 
    GoodsType
values
    ('服装内衣'),('鞋包配饰'),('手机数码')
    
insert into
    GoodsInfo 
values 
    ('提花小西装','红色','菲曼琪',300,1),
    ('百搭短裤','绿色','哥弟',100,1),
    ('无袖背心','白色','阿依莲',700,1),
    ('低帮休闲鞋','红色','菲曼琪',900,2),
    ('中跟单鞋','绿色','哥弟',400,2),
    ('平底鞋','白色','阿依莲',200,2),
    ('迷你照相机','红色','尼康',500,3),
    ('硬盘','黑色','希捷',600,3),
    ('显卡','黑色','技嘉',800,3)\

go

-- 3、查询价格最贵的商品名称，商品名称和商品价格，要求使用别名显示列名
select top 1
    GoodsName 商品名称,GoodsColor 商品名称,GoodsMoney 商品价格 
from 
    GoodsInfo 
group by 
    GoodsName,GoodsColor,GoodsMoney
order by 
    GoodsMoney 
desc

-- 4、按商品类型编号分组查询商品最高价格，最低价格和平均价格，要求使用别名显示列名
select 
    TypeID,max(GoodsMoney),min(GoodsMoney),avg(GoodsMoney) 
from
    GoodsInfo
group by 
    TypeID

--5、查询商品信息所有列，要求商品颜色为红色，价格在300~600之间
select 
    * 
from 
    GoodsInfo 
where
    GoodsColor='红色' and GoodsMoney between 300 and 600
